using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace FederationMembershipDB
{
	public class EmergencyContact
	{
        int _ID;
		string _Name;
		string _Relationship;
		string _Telephone;
		string _Mobile;

        public int ID
        {
            get { return _ID; }
        }

		public void AddEmergencyContact(string name, string relationship, string telephone, string mobile)
		{
            _Name = name;
            _Relationship = relationship;
            _Telephone = telephone;
            _Mobile = mobile;

            //Add to database
            Utilities.openDatabase();

            SqlCommand getNextID = new SqlCommand(string.Format("SELECT ISNULL(MAX(ID) + 1, 1) FROM EmergencyContact"), Utilities.connection);

            _ID = (int)(getNextID.ExecuteScalar());

            SqlCommand insertEmergencyContact = new SqlCommand(string.Format("INSERT INTO EmergencyContact VALUES({0}, '{1}', '{2}', '{3}', '{4}')",
                                                                             _ID, _Name, _Relationship, _Telephone, _Mobile), Utilities.connection);
            insertEmergencyContact.ExecuteNonQuery();
            Utilities.connection.Close();
		}

		public string[] ViewEmergencyContact(int id)
		{
            string[] ret = new string[4];
            _ID = id;

            //retreive member from database
            Utilities.openDatabase();

            SqlCommand getdata = new SqlCommand(string.Format("SELECT e.Name, e.Relationship, e.Telephone, e.Mobile FROM EmergencyContact e, Member m WHERE m.ID = {0} AND e.ID = m.EmergencyContact_ID", _ID), Utilities.connection);
            SqlDataReader dr1 = getdata.ExecuteReader();

            if (dr1.HasRows)
            {
                dr1.Read();
                for (int i = 0; i < 4; i++)
                {
                    ret[i] = (string)dr1[i];
                }

                Utilities.connection.Close();
                return ret;
            }
            else
            {
                Utilities.connection.Close();
                return null;
            }
		}

        public void UpdateEmergencyContact(int id, string name, string relationship, string telephone, string mobile)
		{
            Utilities.openDatabase();

            _ID = id;
            _Name = name;
            _Relationship = relationship;
            _Telephone = telephone;
            _Mobile = mobile;

            SqlCommand UpdateEmergencyContact = new SqlCommand(string.Format("UPDATE EmergencyContact SET Name = '{0}', Relationship = '{1}', Telephone = '{2}', Mobile = '{3}' WHERE ID IN (SELECT Emergencycontact_ID FROM Member m WHERE m.ID = {4}) ",
                                                                 _Name, _Relationship, _Telephone, _Mobile, _ID), Utilities.connection);
            UpdateEmergencyContact.ExecuteNonQuery();

            Utilities.connection.Close();
		}

	}
}
